﻿using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WPFWebControl
{
    public partial class MainWindow : System.Windows.Window, INotifyPropertyChanged
    {
        private void OnLoadFromExcel(object sender, System.Windows.RoutedEventArgs e)
        {
            Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog();

            // Set filter for file extension and default file extension 
            dlg.DefaultExt = ".xlsx";
            dlg.Filter = "Excel 2007 Files (*.xlsx)|*.xlsx|Excel Files (*.xls)|*.xls";
            // Display OpenFileDialog by calling ShowDialog method 
            Nullable<bool> result = dlg.ShowDialog();
            // Get the selected file name and display in a TextBox 
            if (result == true)
            {
                loadLinkFromExcel(dlg.FileName);
            }
        }

        private void loadLinkFromExcel(string sPath)
        {
            string filename = sPath;

            Microsoft.Office.Interop.Excel.Application appExcel;
            Workbook newWorkbook = null;
            _Worksheet objsheet = null;

            appExcel = new Microsoft.Office.Interop.Excel.Application();

            if (System.IO.File.Exists(filename))
            {
                // then go and load this into excel
                newWorkbook = appExcel.Workbooks.Open(filename, true, true);
                objsheet = (_Worksheet)appExcel.ActiveWorkbook.ActiveSheet;
            }
            else
            {

                System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
            }
            //Read data excel

            int nRow = objsheet.Rows.Count;

            if (nRow > 0)
                _listLinks.Clear();

            try
            {

            }
            catch (System.Exception ex)
            {

            }
            for (int i = 1; i < nRow; i++)
            {

                string cellName = String.Format("A{0}", i);
                string sName = String.Empty;
                try
                {
                    sName = objsheet.get_Range(cellName).get_Value().ToString();
                }
                catch
                {

                }
                cellName = String.Format("B{0}", i);
                string sTitle = String.Empty;
                try
                {
                    sTitle = objsheet.get_Range(cellName).get_Value().ToString();
                }
                catch
                {

                }

                if (sName == String.Empty)
                    break;
                else
                {
                    tblLinkBuck record = new tblLinkBuck();
                    record.memberId = 0;
                    record.LinkBuck = sName;
                    record.RealLink = sTitle;

                    if (db != null)
                    {
                        db.tblLinkBucks.Add(record);
                    }
                }
            }

            if (db != null)
            {
                db.SaveChanges();
            }

            //Release excel
            if (appExcel != null)
            {
                try
                {
                    newWorkbook.Close();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
                    appExcel = null;
                    objsheet = null;
                }
                catch (Exception ex)
                {
                    appExcel = null;

                }
                finally
                {
                    GC.Collect();
                }
            }


        }

    }
}
